USE dryad;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


/*==============================================================*/
/* Table: sys_oauth_client_details                              */
/*==============================================================*/
DROP TABLE IF EXISTS `t_oauth_client_details`;
CREATE TABLE `t_oauth_client_details` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `client_id` varchar(32) NOT NULL,
  `resource_ids` varchar(256) DEFAULT NULL,
  `client_secret` varchar(256) DEFAULT NULL,
  `scope` varchar(256) DEFAULT NULL,
  `authorized_grant_types` varchar(256) DEFAULT NULL,
  `web_server_redirect_uri` varchar(256) DEFAULT NULL,
  `authorities` varchar(256) DEFAULT NULL,
  `access_token_validity` int(11) DEFAULT NULL,
  `refresh_token_validity` int(11) DEFAULT NULL,
  `additional_information` varchar(4096) DEFAULT NULL,
  `autoapprove` varchar(256) DEFAULT NULL,
  `archived_flag` char(1) DEFAULT '0' comment '1:已删除 0:未删除',
  `created_by`           varchar(64),
  `created_time`         datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by`           varchar(64),
  `updated_time`         datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ori_id`               varchar(32),
  `reversion`            bigint(20) unsigned default 0,
  `rsv1`                 varchar(128),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='终端信息表';
create unique index idx_oauth_client_details_ci on t_oauth_client_details
(
   client_id
);
BEGIN;
INSERT INTO `t_oauth_client_details` VALUES (1, 'app', NULL, 'app', 'server', 'password,refresh_token,authorization_code,client_credentials,implicit', NULL, NULL, 43200, 2592001, '{\"enc_flag\":\"1\",\"captcha_flag\":\"1\"}', 'true', '0',  'sys','2020-01-22 19:00:23', 'sys', '2020-05-18 14:56:06', NULL, '0', NULL);
COMMIT;

/*==============================================================*/
/* Table: t_customer_operator                                   */
/*==============================================================*/
drop table if exists t_customer_operator;
create table t_customer_operator
(
   id                   bigint(20) unsigned not null,
   operator_no          varchar(32) not null comment '操作员编号',
   operator_name        varchar(64)  comment '操作员名称',
   avatar               varchar(2048) comment '头像',
   gender               char(1) comment 'M-男性；F-女性；N-未知',
   nick_name            varchar(64) comment '昵称',
   operator_lvl         varchar(8) comment '间习-NOVICE 精英-ELITE 大师-MASTER 领袖-LEADER',
   dept_id              int(11) unsigned DEFAULT NULL COMMENT '部门ID',
   login_pwd            varchar(64) not null comment '登录密码',
   login_salt           varchar(48) default '12345678' comment '登陆密码盐',
   forbidden_login_reason varchar(128) comment '禁止登录原因，SS-申述禁止 AQ-安全禁止',
   realname_status      char(1) default 'N' comment 'Y-已经认证；N-未认证；
                                                        描述该操作员是否通过认证；
                                                        只有操作员发起认证，且通过的情况；
                                                        或者操作员发起关联认证通过后，该状态才改为Y，否则均为N；
                                                        默认未认证',
   last_login_success_time datetime comment '最后成功登陆时间',
   last_login_fail_time datetime comment '最后失败登陆时间',
   login_pwd_lock_num   tinyint unsigned comment '登陆密码错误次数',
   login_pwd_lock_date  date comment '登陆密码错误锁定日期',
   last_pwd_modify_time datetime  comment '最后一次修改密码时间',
   last_login_ip        varchar(32)  comment '最后登陆ip',
   auth_time            datetime  comment '认证时间',
   stsword              varchar(128) comment '客户状态字,用每一位来表示不同的冻结状态
                                              BIT1：申述冻结
                                              BIT2：
                                              增加多操作员冻结',
   identity             varchar(16) comment '身份(用户性质)',
   data_source_type     varchar(16) comment '数据来源类型 NORMAL-自然流入
                                             RECOMMEND-好友推荐
                                             REPLACEREG-代注册',
   login_right_range    varchar(8) comment '登陆权限范围 USER-用户前台
                                            SYS-系统内部
                                            WORK-工作平台
                                            ALL-所有',
   operator_status      varchar(8) not null default 'NORMAL' comment '状态 NORMAL-正常
                                                                      FREEZE-冻结
                                                                      LOCK-锁定
                                                                      FORBIDDEN-禁止登陆
                                                                      INACTIVE-未激活',
   archived_by          varchar(64),
   archived_time        datetime comment '归档时间',
   archived_flag        char(1) default '0' comment '归档标志(注销账户) 0-未归档 1-已归档(注销账户)（enabled)',
   locked_flag          char(1) default 'N' comment '锁定标识(禁止登陆) N-未锁定 Y-已锁定(accountNonLocked)',
   locked_time          datetime comment '锁定时间',
   expire_date          date comment '有效期 accountNonExpired',
   credentials_expire_date date comment '密码有效期 credentialsNonExpired',
   pwd_create_type      varchar(8) comment '登陆密码生成方式 CUSTOMER客户填写 SYSTEM系统生成',
   pwd_reset_flag       char(1) default 'N' comment '登陆密码重置标志 N-不需要重置 Y-需要重置',
   remark               varchar(255) comment '备注',
   created_by           varchar(64),
   created_time         datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by           varchar(64),
   updated_time         datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   ori_id               varchar(32),
   reversion            bigint(20) unsigned default 0,
   rsv1                 varchar(128),
   primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='操作员信息';
create index idx_customer_operator_nn on t_customer_operator
(
   nick_name
);
create unique index idx_customer_operator_op on t_customer_operator
(
   operator_no
);
create  index idx_customer_operator_di on t_customer_operator
(
   dept_id
);
create index idx_customer_operator_ct on t_customer_operator
(
   created_time
);
BEGIN;
INSERT INTO `t_customer_operator` (`id`,`operator_no`,`operator_name`,`avatar`,`gender`,`nick_name`,`operator_lvl`,`login_pwd`,`login_salt`,`forbidden_login_reason`,`realname_status`,`last_login_success_time`,`last_login_fail_time`,`login_pwd_lock_num`,`login_pwd_lock_date`,`last_pwd_modify_time`,`last_login_ip`,`auth_time`,`stsword`,`identity`,`data_source_type`,`login_right_range`,`operator_status`,`archived_by`,`archived_time`,`archived_flag`,`locked_flag`,`locked_time`,`expire_date`,`credentials_expire_date`,`pwd_create_type`,`pwd_reset_flag`,`remark`,`created_by`,`created_time`,`updated_by`,`updated_time`,`ori_id`,`reversion`,`rsv1`)
VALUES (1319472307209465857,'71783724a43056454','hulk','','F','hulk','NOVICE','$2a$10$IVzj1Wd.ZQdOIWdb1htQjexU94uoNeuk1crlQ9ExVupPi0Iy1uv.C','w3v6xudvtff5n4cj',NULL,'N',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'NORMAL','ALL','NORMAL',NULL,NULL,'0','N',NULL,NULL,NULL,'sys',NULL,NULL,'dryad','2020-10-23 10:54:47',NULL,'2020-12-21 18:47:42',NULL,0,'hxkkZKPrZmSuPQxWdDEpNGuqezpUJhmv');
COMMIT;


/*==============================================================*/
/* Table: t_operator_security                                   */
/*==============================================================*/
/*drop table if exists t_operator_security;
create table t_operator_security
(
    id                   bigint(20) unsigned not null,
    operator_no          varchar(32) not null comment '操作员编号',
    expire_date          date comment '有效期 ',
    reset_flag           char(1) default 'N' comment '秘钥重置标识 N-不需要重置 Y-需要重置',
    created_by           varchar(64),
    created_time         datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by           varchar(64),
    updated_time         datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ori_id               varchar(32),
    reversion            bigint(20) unsigned default 0,
    rsv1                 varchar(128),
    primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='操作员秘钥信息';
create unique index idx_customer_operator_op on t_customer_operator
(
   operator_no
);*/
/*==============================================================*/
/* Table: t_customer_login_info                                 */
/*==============================================================*/
drop table if exists t_customer_login_info;
create table t_customer_login_info
(
   id                   bigint(20) unsigned not null,
   login_no             varchar(64) not null comment '登录用户号(第三方系统的唯一ID)',
   login_type           varchar(8) not null comment 'PWD-账号密码登录
                                                     SMS-验证码登录
                                                     EMAIL-邮箱
                                                     WECHAT_OPEN-微信开放平台社交登录
                                                     WECHAT_MP-微信公众社交登录
                                                     ALIPAY-支付宝社交登陆
                                                     QQ-QQ社交登陆',
   operator_no          varchar(32) not null comment '操作员编号',
   ori_login_no         varchar(32) comment '修改时，备份之前的登录号',
   login_status         varchar(8) not null default 'NORMAL' comment 'NORMAL-正常
                                                                      FREEZE-冻结
                                                                      LOCK-锁定
                                                                      FORBIDDEN-禁止登陆
                                                                      INACTIVE-未激活',
   active_code          varchar(16) comment '登录号签约渠道',
   login_sign_channel   varchar(8) comment 'WEB-网站用户注册 APP-App用户注册 OTHER-其他渠道',
   access_token         varchar(512) comment '用户的授权令牌',
   expire_in            bigint(16) unsigned comment '第三方用户的授权令牌的有效期',
   refresh_token        varchar(512) comment '刷新令牌',
   open_id              varchar(64) comment '第三方用户的OPENID',
   uid                  varchar(64) comment '第三方用户的 ID',
   code                 varchar(512) comment '用户的授权CODE',
   access_code          varchar(256) comment '个别平台的授权CODE',
   union_id             varchar(64) comment '第三方用户的UNIONID',
   scope                varchar(64) comment '第三方用户授予的权限',
   token_type           varchar(32) comment '个别平台的授权信息',
   id_token             varchar(512) comment '',
   mac_algorithm        varchar(64) comment '小米平台用户的附带属性1',
   mac_key              varchar(64) comment '小米平台用户的附带属性2',
   oauth_token          varchar(64) comment 'Twitter平台用户的附带属性1',
   oauth_token_secret   varchar(64) comment 'Twitter平台用户的附带属性2',
   remark               varchar(255) comment '备注',
   created_by           varchar(64),
   created_time         datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   archived_time        datetime,
   archived_by          varchar(64),
   archived_flag        char(1) default '0' comment '0-未归档 1-已归档(注销账户)',
   updated_by           varchar(64),
   updated_time         datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   ori_id               varchar(32),
   reversion            bigint(20) unsigned default 0,
   rsv1                 varchar(128),
   primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='客户登录信息';
create index idx_customer_login_op on t_customer_login_info
(
   operator_no
);
create index idx_customer_login_ct on t_customer_login_info
(
   created_time
);
create unique index idx_customer_login_lnt on t_customer_login_info
(
   login_no
);

BEGIN;
INSERT INTO `t_customer_login_info` (`id`,`login_no`,`login_type`,`operator_no`,`ori_login_no`,`login_status`,`active_code`,`login_sign_channel`,`access_token`,`expire_in`,`refresh_token`,`open_id`,`uid`,`code`,`access_code`,`union_id`,`scope`,`token_type`,`id_token`,`mac_algorithm`,`mac_key`,`oauth_token`,`oauth_token_secret`,`remark`,`created_by`,`created_time`,`archived_time`,`archived_by`,`archived_flag`,`updated_by`,`updated_time`,`ori_id`,`reversion`,`rsv1`)
VALUES (1319472307209465856,'29572320@qq.com','PWD','71783724a43056454',NULL,'NORMAL',NULL,'WEB',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sys','2020-10-23 10:54:47',NULL,NULL,'0',NULL,'2020-12-21 18:42:52',NULL,0,NULL);
COMMIT;

/*==============================================================*/
/* Table: t_sms_send_record                                     */
/*==============================================================*/
drop table if exists t_sms_send_record;
create table t_sms_send_record
(
   id                   bigint(20) unsigned not null,
   sms_id               varchar(32) not null comment '消息号',
   mobile               varchar(32) not null comment '手机号',
   sms_type             varchar(8) not null comment '消息类型',
   sms_code             varchar(16) not null comment '验证码',
   verify_flag          char(1) default 'N' comment ' 验证标识 Y:已验证 N:未验证',
   expire_time          datetime NOT NULL DEFAULT CURRENT_TIMESTAMP comment '过期时间',
   created_by           varchar(64),
   created_time         datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_by           varchar(64),
   updated_time         datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   reversion            bigint(20) unsigned default 0,
   ori_id               varchar(32),
   rsv1                 varchar(128),
   primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='短息发送记录';
create index idx_sms_send_record_ct on t_sms_send_record
(
   created_time
);
create index idx_sms_send_record_m on t_sms_send_record
(
   mobile
);



SET FOREIGN_KEY_CHECKS = 1;